﻿using H.Dao.SQLite.Daos.Cache;
using H.Daos;
using H.Logs;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace H.Dao.SQLite.Daos
{
    /// <summary>
    /// 数据访问层接口的默认实现
    /// </summary>
    public class HDao : IDao
    {
        /// <summary>
        /// 数据库连接实例
        /// </summary>
        private readonly SQLiteConnection _Connection;

        /// <summary>
        /// 日志收集器实例
        /// </summary>
        internal ILogCollector LogCollector;

        /// <summary>
        /// 日志对象
        /// </summary>
        private readonly ILog Log;


        /// <summary>
        /// 初始化一个Dao
        /// </summary>
        /// <param name="dbFile"></param>
        /// <param name="password"></param>
        /// <param name="isReadonly"></param>
        public HDao(string dbFile, string password, bool isReadonly = false)
        {
            LogCollector = IPlugin.GetObject<ILoggerFactory>("H.Logger")?.GetInstance();
            Log = LogCollector?.GetInstance("HDao");

            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
            {
                DataSource = dbFile,
                Password = password,
                ReadOnly = isReadonly
            };
            _Connection = new SQLiteConnection(builder.ToString());
            _Connection.Open();
        }

        ///<inheritdoc/>
        public void UpdateTable<M>() where M : AbstractEntity
        {
            SQLiteCommand cmd = _Connection.CreateCommand();

            string sql = "PRAGMA table_info(" + ModelMapping<M>.TableName + ")";
            cmd.CommandText = sql;

            Dictionary<string, TableInfo> existColumns = null;
            using (SQLiteDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    //能够查到,更新表
                    existColumns = new Dictionary<string, TableInfo>();
                    while (reader.Read())
                    {
                        string columnName = reader.GetString(reader.GetOrdinal("name"));
                        existColumns.Add(columnName, new TableInfo
                        {
                            name = columnName,
                            type = (DbType)Enum.Parse(typeof(DbType), reader.GetString(reader.GetOrdinal("type"))),
                            notnull = reader.GetBoolean(reader.GetOrdinal("notnull")),
                            defaultvalue = reader["dflt_value"]?.ToString(),
                            primarykey = reader.GetBoolean(reader.GetOrdinal("pk"))
                        });
                    }
                }
            }
            #region 生成创建表的SQL语句
            string GenTable()
            {
                List<string> primaryKeyList = new List<string>();

                StringBuilder sb = new StringBuilder();
                sb.Append($"CREATE TABLE {ModelMapping<M>.TableName} (");

                bool isFirst = true;
                foreach (FieldMapping column in ModelMapping<M>.ColumnDict.Values)
                {
                    if (isFirst)
                    {
                        isFirst = false;
                    }
                    else
                    {
                        sb.Append(',');
                    }

                    sb.Append(column.ColumnName).Append(" ").Append(column.Type.ToString());

                    if (column.IsPrimaryKey)
                    {
                        if (column.IsAutoIncrement)
                        {
                            sb.Append(" ").Append("PRIMARY KEY AUTOINCREMENT");
                        }
                        else
                        {
                            primaryKeyList.Add(column.ColumnName);
                        }
                    }

                    if (column.DefaultValue != null && column.DefaultValue.Length > 0)
                    {
                        sb.Append(" ").Append("DEFAULT ").Append(column.DefaultValue);
                    }

                    if (column.IsNotNull || column.IsPrimaryKey)
                    {
                        sb.Append(" ").Append("NOT NULL");
                    }

                    if (column.IsUnique)
                    {
                        sb.Append(" ").Append("UNIQUE");
                    }
                }
                if (primaryKeyList.Count > 0)
                {
                    sb.Append(",PRIMARY KEY (").Append(string.Join(",", primaryKeyList)).Append(")");
                }
                sb.Append(");");
                return sb.ToString();
            }
            #endregion

            if (existColumns != null)
            {
                bool shouldUpdate = false;
                //检查是否需要更新表
                foreach (FieldMapping c in from i in ModelMapping<M>.ColumnDict select i.Value)
                {
                    if (existColumns.TryGetValue(c.ColumnName, out TableInfo info))
                    {
                        if (c.IsPrimaryKey != info.primarykey)
                        {
                            shouldUpdate = true;
                            break;
                        }
                        else if ((c.IsPrimaryKey || c.IsNotNull) != info.notnull)
                        {
                            shouldUpdate = true;
                            break;
                        }
                        else if (!Equals(c.DefaultValue, info.defaultvalue))
                        {
                            shouldUpdate = true;
                            break;
                        }
                        else if (c.Type != info.type)
                        {
                            shouldUpdate = true;
                            break;
                        }
                    }
                    else
                    {
                        shouldUpdate = true;
                    }
                }

                if (shouldUpdate)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append($"ALTER TABLE {ModelMapping<M>.TableName} RENAME TO tempTable;");

                    sb.Append(GenTable());
                    string orginKey = string.Join(",", from i in existColumns select i.Key);
                    sb.Append($"INSERT INTO {ModelMapping<M>.TableName} ({orginKey}) SELECT {orginKey} FROM tempTable;");

                    sb.Append($"DROP TABLE tempTable");

                    cmd.CommandText = sb.ToString();
                    cmd.ExecuteNonQuery();

                    Log?.Debug($"数据库表{ModelMapping<M>.TableName}已更新");
                }
                else
                {
                    Log?.Debug($"数据库表{ModelMapping<M>.TableName}已经是最新的版本");
                }
            }
            else
            {
                //新增表
                cmd.CommandText = GenTable();
                cmd.ExecuteNonQuery();

                Log?.Debug($"数据库表{ModelMapping<M>.TableName}已创建");
            }

        }



        ///<inheritdoc/>
        public M FindOne<M>(int id) where M : AbstractEntity, new()
        {
            SQLiteCommand cmd = _Connection.CreateCommand();
            cmd.CommandText = $"SELECT * FROM {ModelMapping<M>.TableName} WHERE {ModelMapping<M>.PrimaryKey}=@ID";
            Log?.Debug("SQL: " + cmd.CommandText);
            cmd.Parameters.AddWithValue("ID", id);

            SQLiteDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                M m = new M();
                foreach (KeyValuePair<string, FieldMapping> kv in ModelMapping<M>.ColumnDict)
                {
                    kv.Value.ReadDbMethods(m, reader);
                }

                reader.Close();

                return m;
            }
            else
            {
                reader.Close();
                return null;
            }
        }

        ///<inheritdoc/>
        public T[] Column<T>(string sql, KeyValuePair<string, object>[] valueBindings = null)
        {
            SQLiteCommand cmd = _Connection.CreateCommand();
            Log?.Debug("SQL: " + cmd.CommandText);
            cmd.CommandText = sql;
            if (valueBindings != null)
            {
                foreach (KeyValuePair<string, object> binding in valueBindings)
                {
                    cmd.Parameters.AddWithValue(binding.Key, binding.Value);
                }
            }

            SQLiteDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                List<T> result = new List<T>();
                do
                {
                    result.Add((T)reader[0]);
                } while (reader.Read());

                reader.Close();
                return result.ToArray();
            }
            else
            {
                reader.Close();
                return null;
            }
        }

        ///<inheritdoc/>
        public T Value<T>(string sql, KeyValuePair<string, object>[] valueBindings = null)
        {
            SQLiteCommand cmd = _Connection.CreateCommand();
            Log?.Debug("SQL: " + cmd.CommandText);
            cmd.CommandText = sql;
            if (valueBindings != null)
            {
                foreach (KeyValuePair<string, object> binding in valueBindings)
                {
                    cmd.Parameters.AddWithValue(binding.Key, binding.Value);
                }
            }

            SQLiteDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                T t = (T)reader[0];
                reader.Close();
                return t;
            }
            else
            {
                reader.Close();
                return default;
            }
        }

        ///<inheritdoc/>
        public M[] Select<M>(string sql, KeyValuePair<string, object>[] valueBindings = null) where M : AbstractEntity, new()
        {
            SQLiteCommand cmd = _Connection.CreateCommand();
            cmd.CommandText = sql;
            Log?.Debug("SQL: " + cmd.CommandText);
            if (valueBindings != null)
            {
                foreach (KeyValuePair<string, object> binding in valueBindings)
                {
                    cmd.Parameters.AddWithValue(binding.Key, binding.Value);
                }
            }

            List<M> result = new List<M>();
            SQLiteDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                M m = new M();
                foreach (KeyValuePair<string, FieldMapping> kv in ModelMapping<M>.ColumnDict)
                {
                    kv.Value.ReadDbMethods(m, reader);
                }

                result.Add(m);
            }

            reader.Close();
            return result.ToArray();
        }

        ///<inheritdoc/>
        public NameValueCollection[] Select(string sql, KeyValuePair<string, object>[] valueBindings = null)
        {
            SQLiteCommand cmd = _Connection.CreateCommand();
            cmd.CommandText = sql;
            Log?.Debug("SQL: " + cmd.CommandText);
            if (valueBindings != null)
            {
                foreach (KeyValuePair<string, object> binding in valueBindings)
                {
                    cmd.Parameters.AddWithValue(binding.Key, binding.Value);
                }
            }

            SQLiteDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                List<NameValueCollection> result = new List<NameValueCollection>();
                do
                {
                    result.Add(reader.GetValues());
                } while (reader.Read());

                reader.Close();
                return result.ToArray();
            }
            else
            {
                reader.Close();
                return null;
            }
        }



        ///<inheritdoc/>
        public void Save<M>(M model, bool allowUpdate = true) where M : AbstractEntity, new()
        {
            SQLiteCommand cmd = _Connection.CreateCommand();

            foreach (FieldMapping column in ModelMapping<M>.ColumnList)
            {
                cmd.Parameters.AddWithValue(column.ColumnName, column.GetMethod(model));
            }
            if (allowUpdate)
            {
                cmd.CommandText = $"INSERT OR REPLACE INTO {ModelMapping<M>.TableName} ({ModelMapping<M>.ColumnNames}) VALUES({ModelMapping<M>.ColumnPlaceHolder})";
            }
            else
            {
                cmd.CommandText = $"INSERT INTO {ModelMapping<M>.TableName} ({ModelMapping<M>.ColumnNames}) VALUES({ModelMapping<M>.ColumnPlaceHolder})";
            }
            Log?.Debug("SQL: " + cmd.CommandText);
            cmd.ExecuteNonQuery();
        }

        ///<inheritdoc/>
        public void ChangePassword(string newPassword)
        {
            _Connection.ChangePassword(newPassword);
        }


        ///<inheritdoc/>
        public IQuery<M> Query<M>(string tableName = null) where M : AbstractEntity, new()
        {
            return new HQuery<M>(this).Table(tableName);
        }

        ///<inheritdoc/>
        public IQuery Query(string tableName)
        {
            return new HQuery(this).Table(tableName);
        }

        ///<inheritdoc/>
        public int Execute(string sql, KeyValuePair<string, object>[] valueBindings = null)
        {
            SQLiteCommand cmd = _Connection.CreateCommand();
            cmd.CommandText = sql;
            if (valueBindings != null)
            {
                foreach (KeyValuePair<string, object> binding in valueBindings)
                {
                    cmd.Parameters.AddWithValue(binding.Key, binding.Value);
                }
            }
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result;
        }

        ///<inheritdoc/>
        public void Delete<M>(M model) where M : AbstractEntity, new()
        {
            SQLiteCommand cmd = _Connection.CreateCommand();

            cmd.Parameters.AddWithValue("value_1", model.ID);
            cmd.CommandText = $"DELETE FROM {ModelMapping<M>.TableName} Where ID=@value_1";
            Log?.Debug("SQL: " + cmd.CommandText);
            cmd.ExecuteNonQuery();
        }



        #region 资源释放
        /// <summary>
        /// 内部释放资源函数
        /// </summary>
        /// <param name="disposing"></param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing) { }
        }

        /// <summary>
        /// 释放Dao所占用的资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        ~HDao()
        {
            Dispose(false);
        }
        #endregion

        /// <summary>
        /// 表示一个表结构,用于自动更新表结构
        /// </summary>
        private struct TableInfo
        {
            public string name;
            public DbType type;
            public bool notnull;
            public string defaultvalue;
            public bool primarykey;
        }
    }
}
